'use strict';

const {Service} = require('ee-core');
const Storage = require('ee-core/storage');
const _ = require('lodash');
const path = require('path');

const spiderJson = `create table spider_json
(
    id     INT,
    json      TEXT,
    create_time datetime,
    update_time datetime
);`

const execRecord = `create table spider_exec_record
(
    id     INT,
    json      TEXT,
    exec_res TEXT,
    create_time datetime,
    update_time datetime
);`


/**
 * Spiderdb（service层为单例）
 * @class
 */
class SpiderdbService extends Service {

    constructor(ctx) {
        super(ctx);

        this.sqliteFile = 'sqlite-demo.db';
        let sqliteOptions = {
            driver: 'sqlite',
            default: {
                timeout: 6000,
                verbose: console.log // 打印sql语法
            }
        }
        this.demoSqliteDB = Storage.connection(this.sqliteFile, sqliteOptions);
    }


    /*
     * 检查并创建表 (spider_json)
     */
    async checkAndCreateTableSqlite(tableName = 'spider_json') {
        if (_.isEmpty(tableName)) {
            throw new Error(`table name is required`);
        }
        // 检查表是否存在
        const userTable = this.demoSqliteDB.db.prepare('SELECT * FROM sqlite_master WHERE type=? AND name = ?');
        const result = userTable.get('table', tableName);
        console.log('result:', result);
        if (result) {
            return;
        }
        // 创建表
        if (tableName === 'spider_json') {
            this.demoSqliteDB.db.exec(spiderJson);
        } else if (tableName === 'spider_exec_record') {
            this.demoSqliteDB.db.exec(execRecord);
        }
    }


    /*
    * 增
    */
    async addConfig(data) {
        let table = 'spider_json';
        await this.checkAndCreateTableSqlite(table);
        const insert = this.demoSqliteDB.db.prepare(`insert into ${table} (id, json, create_time, update_time)
        values (ifnull((SELECT last_insert_rowid()), 0), @Json, @CreateTime, @UpdateTime)`);
        insert.run(data);

        return true;
    }


    /*
     * 获取最近的一条配置
     */
    async getLastConfig(data) {
        let table = 'spider_json';
        await this.checkAndCreateTableSqlite(table);
        const insert = this.demoSqliteDB.db.prepare(`select * from ${table} order by create_time desc limit 1`);
        return insert.get();
    }
}

SpiderdbService.toString = () => '[class SpiderdbService]';
module.exports = SpiderdbService;